﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Globalization;

namespace Web.Ajax.Data
{
	public partial class Sql
	{
		public static string ConvertCol(object c)
		{
			if (c == null)
				return null;
			return "[" + c.ToString() + "]";
		}

		public static string Convert(object item)
		{
			if (item is DBNull || item == null)
			{
				return "NULL";
			}
			if (item is string || item is Guid)
			{
				return "'" + item.ToString().Replace("'", "''") + "'";
			}
			if (item is DateTime)
			{
				CultureInfo ci = new CultureInfo("en-IE");
				string date = "'" + ((DateTime)item).ToString("MM/dd/yyyy HH:mm:ss.fff", ci.DateTimeFormat) + "'";
				return date;
			}
			if (item is bool)
			{
				if ((bool)item)
					return "1";
				else
					return "0";
			}
			Type t = item.GetType();
			if (t.IsEnum && Enum.GetUnderlyingType(t) == typeof(int))
			{
				return ((int)item).ToString();
			}
			return item.ToString().Replace("'", "''");
		}

		public static string WhereAndSql(System.Collections.Hashtable andClause)
		{
			if (andClause == null)
				return null;
			var l = new List<string>();
			foreach (var k in andClause.Keys)
			{
				l.Add(ConvertCol(k) + "=" + Convert(andClause[k]));
			}
			return string.Join(" AND ", l.ToArray());
		}

		public static string WhereAndSql(object clause)
		{
			return WhereAndSql(HashtableExtensions.Convert(clause));
		}

		public static string SetSql(System.Collections.Hashtable values)
		{
			var l = new List<string>();
			foreach (string k in values.Keys)
			{
				l.Add(ConvertCol(k) + " = " + Convert(values[k]));
			}
			return string.Join(", ", l.ToArray());
		}

		public static string SelectSql(string table, System.Collections.Hashtable andClause)
		{
			return SelectSql(table, WhereAndSql(andClause));
		}

		public static string SelectSql(string table, string where)
		{
			var sql = new StringBuilder();
			sql.Append(@"
select * from #table#");
			sql.Replace("#table#", table);
			if (!string.IsNullOrEmpty(where))
			{
				sql.Append(" where #where#");
				sql.Replace("#where#", where);
			}
			sql.Append(";");
			return sql.ToString();
		}
		
		
		public static string InsertSql(string table, System.Collections.Hashtable values, System.Collections.Hashtable clause, bool IdColumnInsertable)
		{		
			var sql = new StringBuilder();
			if (IdColumnInsertable)
				sql.Append(@"
if OBJECTPROPERTY(object_id('#table#'), 'TableHasIdentity') = 1
	set identity_insert #table# on
");


			if (clause != null)
			{
				sql.Append(@"
if (select count(1) from #table# where #clause#) = 0
	");
				sql.Replace("#clause#", WhereAndSql(clause));
			}
			
			sql.Append(@"
insert #table# (#cols#) values (#vals#); ");

			if (IdColumnInsertable)
				sql.Append(@"
if OBJECTPROPERTY(object_id('#table#'), 'TableHasIdentity') = 1
	set identity_insert #table# off
");


			var cols = new List<string>();
			var vals = new List<string>();

			foreach (var k in values.Keys)
			{
				cols.Add(ConvertCol(k));
				vals.Add(Convert(values[k]));
			}

			sql.Replace("#table#", table);
			sql.Replace("#cols#", string.Join(", ", cols.ToArray()));
			sql.Replace("#vals#", string.Join(", ", vals.ToArray()));
			return sql.ToString();
		}
		public static string InsertSql(string table, System.Collections.Hashtable values, bool IdColumnInsertable)
		{
			return InsertSql(table, values, null, IdColumnInsertable);
		}
		public static string InsertSql(string table, System.Collections.Hashtable values)
		{
			return InsertSql(table, values, null, false);
		}


		public static string UpdateSql(string table, System.Collections.Hashtable values, System.Collections.Hashtable AndClause)
		{
			var sql = new StringBuilder();
			sql.Append(@"
update #table# set #set# where #where#; ");			
			sql.Replace("#table#", table);
			sql.Replace("#set#", SetSql(values));
			sql.Replace("#where#", WhereAndSql(AndClause));
			return sql.ToString();
		}

		public static string DeleteSql(string table, System.Collections.Hashtable AndClause)
		{
			var sql = new StringBuilder();
			sql.Append(@"
delete from #table# where #where#;");

			sql.Replace("#table#", table);
			sql.Replace("#where#", WhereAndSql(AndClause));
			return sql.ToString();
		}

		public static string SaveSql(string table, System.Collections.Hashtable insertValues, System.Collections.Hashtable updateValues, System.Collections.Hashtable andClause, bool idColumnInsertable)
		{
			if (andClause == null)
				return InsertSql(table, insertValues, idColumnInsertable);

			var sql = new StringBuilder();
			sql.Append(@"
if (select count(1) from #table# where #where#) > 0	
begin #update#
end
else 
begin #insert#
end
");

			sql.Replace("#table#", table);
			sql.Replace("#where#", WhereAndSql(andClause));
			sql.Replace("#insert#", InsertSql(table, insertValues, idColumnInsertable));
			sql.Replace("#update#", UpdateSql(table, updateValues, andClause));

			return sql.ToString();
		}

		public static StringBuilder TruncateSql(string table)
		{
			var sql = new StringBuilder("truncate table #table#;");
			sql.Replace("#table#", table);
			return sql;
		}

		public static StringBuilder UpdateStatisticsSql(string table)
		{
			var sql = new StringBuilder("update statistics #table#;");
			sql.Replace("#table#", table);
			return sql;
		}

		
	}
}
